﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Data;
using System.Data.SqlClient;

namespace Petaframe.DataAccess
{
    /// <summary>
    /// SqlServer veritabanına özel işlemler için kullanılır.
    /// </summary>
    public class SqlServerDataService : DataService , ISqlServerDataService
    {
        
        public SqlServerDataService(string serviceConnectionString)
            : base(serviceConnectionString, new SqlServerParameterBuilder(), new SqlServerEscapeSqlIdentifier(),DbProviderFactories.GetFactory("System.Data.SqlClient"))
        {
                  
        }
      
        //public override object Insert<T>(T item)
        //{
        //    //TableInfo table = ClassDecomposer.ForEntity(item, new SqlServerParameterBuilder(), new SqlServerEscapeSqlIdentifier());
        //    var info = EntityQueryCache.GetTableQueriesAndInfo(item, _parameterBuilder, _escapeIdentifier);
        //    ISqlCommandStringBuilder insertCommand;

        //    OpenConnection();

        //    //eğer tabloda primary key varsa insert işleminden sonra primarykey değerini döndür          
        //    if (info.Item2.HasPrimaryKey)
        //    {
        //        insertCommand =new  SqlServerReturningInsertCommandBuilder();
        //        DbCommand.CommandText = insertCommand.CommandString(info.Item2).ToSqlCommandString();
        //    }
        //    else//primarykey yoksa int değer döndüren komutu oluştur
        //    {
        //        insertCommand = new SqlInsertCommandBuilder();
        //        DbCommand.CommandText = insertCommand.CommandString(info.Item2).ToSqlCommandString();
        //    }


        //    foreach (var column in info.Item2.Properties)
        //    {
        //       // ParameterInfo paramInfo = info.Item2.Parameters[column.PropertyName];
        //        if (!column.AutoIncrement)
        //        {
        //            DbParameter parameter = column.ParameterInfo.DatabaseParameter;

        //            parameter.Value = ParameterInfo.GetParameterValue(column.CurrentPropertyDetails, item) ?? DBNull.Value;

        //            DbCommand.Parameters.Add(parameter);
        //        }
        //    }

        //    object result = info.Item2.HasPrimaryKey ? DbCommand.ExecuteScalar() : DbCommand.ExecuteNonQuery(); ;
        //    CloseConnection();
        //    return result;
        //}

        /// <summary>
        /// Sorgu sonucunu sayfalamak için kullanılır.
        /// </summary>
        /// <typeparam name="T">Sorgu için kullanılacak sınıf.</typeparam>
        /// <param name="currentPage">Sayfa numarası.</param>
        /// <param name="pageSize">Bir sayfada gösterilecek olan satır sayısı.</param>
        /// <param name="whereCommand">Where komutu.</param>
        /// <param name="orderByCommand">Order by komutu.</param>
        /// <returns><see cref="PagedResults"/> sınıfı içine konulmuş sorgu sonucu </returns>
        public override PagedResults<T> Page<T>(int currentPage = 1, int pageSize = 20, string whereCommand = "", string orderByCommand = "")
        {
            //TableInfo table = ClassDecomposer.ForType(typeof(T), new SqlServerParameterBuilder(), new SqlServerEscapeSqlIdentifier());
            var table = EntityQueryCache.GetCachedTableInfo(Activator.CreateInstance<T>(), _parameterBuilder, _escapeIdentifier, _dbProviderFactory);
            PagedResults<T> page = new PagedResults<T>();

            currentPage = currentPage < 1 ? 1 : currentPage;
            pageSize = pageSize < 1 ? 1 : pageSize;

            if (!string.IsNullOrEmpty(whereCommand))
            {
                if (!whereCommand.Trim().StartsWith("where", StringComparison.OrdinalIgnoreCase))
                {
                    whereCommand = "WHERE " + whereCommand;
                }               
            }

            var pageStart = (currentPage - 1) * pageSize;

          string cmd =string.Format("SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY {0}) AS Row, * FROM {1} {2} ) as Paged WHERE Row > {3} AND Row <={4}  {5}", table.PrimaryKeys.Count > 0 ? table.PrimaryKeys[0].PropertyName : table.Properties[0].PropertyName, table.TableName, whereCommand, pageStart, (pageStart + pageSize), orderByCommand);

            //toplam satır sayısını al           
           page.TotalRecords =(int) GetScalarValue(string.Format("select count(*) from {0} {1}", table.TableName,whereCommand));            

            page.TotalPages = page.TotalRecords / pageSize;
            if (page.TotalRecords % pageSize > 0)
                page.TotalPages += 1;

            page.items = FindAll<T>(cmd);
           
            return page;
        }


    
    }
}
